SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

<title>Transact-SQL</title>

</head>

Transact-SQL

</h1>

</header>

/* start https://en.wikipedia.org/ */

.mw-parser-output .hatnote{font-style:italic}.mw-parser-output div.hatnote{padding-left:1.6em;margin-bottom:0.5em}.mw-parser-output .hatnote i{font-style:normal}.mw-parser-output .hatnote+link+.hatnote{margin-top:-0.5em}@media print{body.ns-0 .mw-parser-output .hatnote{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><div role="note" class="hatnote navigation-not-searchable">"TSQL" redirects here. For the proposed temporal extension of SQL, see <a href="TSQL2" class="mw-redirect" title="TSQL2">TSQL2</a>.</div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .ambox{border:1px solid #a2a9b1;border-left:10px solid #36c;background-color:#fbfbfb;box-sizing:border-box}.mw-parser-output .ambox+link+.ambox,.mw-parser-output .ambox+link+style+.ambox,.mw-parser-output .ambox+link+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+style+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+link+.ambox{margin-top:-1px}html body.mediawiki .mw-parser-output .ambox.mbox-small-left{margin:4px 1em 4px 0;overflow:hidden;width:238px;border-collapse:collapse;font-size:88%;line-height:1.25em}.mw-parser-output .ambox-speedy{border-left:10px solid #b32424;background-color:#fee7e6}.mw-parser-output .ambox-delete{border-left:10px solid #b32424}.mw-parser-output .ambox-content{border-left:10px solid #f28500}.mw-parser-output .ambox-style{border-left:10px solid #fc3}.mw-parser-output .ambox-move{border-left:10px solid #9932cc}.mw-parser-output .ambox-protection{border-left:10px solid #a2a9b1}.mw-parser-output .ambox .mbox-text{border:none;padding:0.25em 0.5em;width:100%}.mw-parser-output .ambox .mbox-image{border:none;padding:2px 0 2px 0.5em;text-align:center}.mw-parser-output .ambox .mbox-imageright{border:none;padding:2px 0.5em 2px 0;text-align:center}.mw-parser-output .ambox .mbox-empty-cell{border:none;padding:0;width:1px}.mw-parser-output .ambox .mbox-image-div{width:52px}@media(min-width:720px){.mw-parser-output .ambox{margin:0 10%}}@media print{body.ns-0 .mw-parser-output .ambox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r1248332772">

/* start https://en.wikipedia.org/ */

.mw-parser-output .multiple-issues-text{width:95%;margin:0.2em 0}.mw-parser-output .multiple-issues-text>.mw-collapsible-content{margin-top:0.3em}.mw-parser-output .compact-ambox .ambox{border:none;border-collapse:collapse;background-color:transparent;margin:0 0 0 1.6em!important;padding:0!important;width:auto;display:block}body.mediawiki .mw-parser-output .compact-ambox .ambox.mbox-small-left{font-size:100%;width:auto;margin:0}.mw-parser-output .compact-ambox .ambox .mbox-text{padding:0!important;margin:0!important}.mw-parser-output .compact-ambox .ambox .mbox-text-span{display:list-item;line-height:1.5em;list-style-type:disc}body.skin-minerva .mw-parser-output .multiple-issues-text>.mw-collapsible-toggle,.mw-parser-output .compact-ambox .ambox .mbox-image,.mw-parser-output .compact-ambox .ambox .mbox-imageright,.mw-parser-output .compact-ambox .ambox .mbox-empty-cell,.mw-parser-output .compact-ambox .hide-when-compact{display:none}

/* end https://en.wikipedia.org/ */

</style>

Transact-SQL (T-SQL) is <a href="Microsoft" title="Microsoft">Microsoft</a>'s and <a href="Sybase" title="Sybase">Sybase</a>'s proprietary extension to the <a href="SQL" title="SQL">SQL</a> (Structured Query Language) used to interact with <a href="Relational_database" title="Relational database">relational databases</a>. T-SQL expands on the SQL standard to include <a href="Procedural_programming" title="Procedural programming">procedural programming</a>, <a href="Local_variable" title="Local variable">local variables</a>, various support functions for string processing, date processing, mathematics, etc. and changes to the <a href="Delete_(SQL)" title="Delete (SQL)">DELETE</a> and <a href="Update_(SQL)" title="Update (SQL)">UPDATE</a> statements.

Transact-SQL is central to using <a href="Microsoft_SQL_Server" title="Microsoft SQL Server">Microsoft SQL Server</a>. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

<a href="Stored_procedure" title="Stored procedure">Stored procedures</a> in SQL Server are executable server-side routines. The advantage of stored procedures is the ability to pass parameters.

<div class="mw-heading mw-heading2"><h2 id="Variables">Variables</h2></div>

Transact-SQL provides the following statements to declare and set local variables: <code>DECLARE</code>, <code>SET</code> and <code>SELECT</code>.

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>DECLARE @var1 NVARCHAR(30);

SET @var1 = 'Some Name';

SELECT @var1 = Name

FROM Sales.Store

WHERE CustomerID = 100;

</pre></div>

<div class="mw-heading mw-heading2"><h2 id="Flow_control">Flow control</h2></div>

Keywords for flow control in Transact-SQL include <code>BEGIN</code> and <code>END</code>, <code>BREAK</code>, <code>CONTINUE</code>, <code>GOTO</code>, <code>IF</code> and <code>ELSE</code>, <code>RETURN</code>, <code>WAITFOR</code>, and <code>WHILE</code>.

<code>IF</code> and <code>ELSE</code> allow conditional execution. This batch statement will print "It is the weekend" if the current date is a weekend day, or "It is a weekday" if the current date is a weekday. (Note: This code assumes that Sunday is configured as the first day of the week in the <code>@@DATEFIRST</code> setting.)

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1

PRINT 'It is the weekend.';

ELSE

PRINT 'It is a weekday.';

</pre></div>

<code>BEGIN</code> and <code>END</code> mark a <a href="Block_of_statements" class="mw-redirect" title="Block of statements">block of statements</a>. If more than one statement is to be controlled by the conditional in the example above, we can use <code>BEGIN</code> and <code>END</code> like this:

BEGIN

PRINT 'It is the weekend.';

PRINT 'Get some rest on the weekend!';

END;

ELSE

BEGIN

PRINT 'It is a weekday.';

PRINT 'Get to work on a weekday!';

END;

</pre></div>

<code>WAITFOR</code> will wait for a given amount of time, or until a particular time of day. The statement can be used for delays or to block execution until the set time.

<code>RETURN</code> is used to immediately return from a <a href="Stored_procedure" title="Stored procedure">stored procedure</a> or function.

<code>BREAK</code> ends the enclosing <code>WHILE</code> loop, while <code>CONTINUE</code> causes the next iteration of the loop to execute. An example of a <code>WHILE</code> loop is given below.

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>DECLARE @i INT;

SET @i = 0;

WHILE @i < 5

BEGIN

PRINT 'Hello world.';

SET @i = @i + 1;

END;

</pre></div>

<div class="mw-heading mw-heading2"><h2 id="Changes_to_DELETE_and_UPDATE_statements">Changes to DELETE and UPDATE statements</h2></div>

In Transact-SQL, both the <code>DELETE</code> and <code>UPDATE</code> statements are enhanced to enable data from another table to be used in the operation, without needing a subquery:

<ul><li><code>DELETE</code> accepts joined tables in the <code>FROM</code> clause, similarly to <code>SELECT</code>. When this is done, the name or alias of which table in the join is to be deleted from is placed between <code>DELETE</code> and <code>FROM</code>.</li>

<li><code>UPDATE</code> allows a <code>FROM</code> clause to be added. The table to be updated can be either joined in the <code>FROM</code> clause and referenced by alias, or referenced only at the start of the statement as per standard SQL.</li></ul>

This example deletes all <code class="mw-highlight mw-highlight-lang-text mw-content-ltr" style="" dir="ltr">users</code> who have been flagged in the <code class="mw-highlight mw-highlight-lang-text mw-content-ltr" style="" dir="ltr">user_flags</code> table with the 'idle' flag.

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>DELETE u

FROM users AS u INNER JOIN user_flags AS f ON u.id = f.id

WHERE f.name = 'idle';

</pre></div>

<div class="mw-heading mw-heading2"><h2 id="BULK_INSERT">BULK INSERT</h2></div>

<code>BULK</code> is a Transact-SQL statement that implements a bulk data-loading process, inserting multiple rows into a table, reading data from an external sequential file. Use of <code>BULK INSERT</code> results in better performance than processes that issue individual <code>INSERT</code> statements for each row to be added. Additional details are available <a rel="nofollow" class="external text" href="http://msdn2.microsoft.com/en-us/library/ms188365.aspx">in MSDN</a>.

<div class="mw-heading mw-heading2"><h2 id="TRY_CATCH">TRY CATCH</h2></div>

Beginning with SQL Server 2005,<a href="#cite_note-1">[1]</a> Microsoft introduced additional <code>TRY CATCH</code> logic to support exception type behaviour. This behaviour enables developers to simplify their code and leave out <code>@@ERROR</code> checking after each SQL execution statement.

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>-- begin transaction

BEGIN TRAN;

BEGIN TRY

-- execute each statement

INSERT INTO MYTABLE(NAME) VALUES ('ABC');

-- commit the transaction

COMMIT TRAN;

END TRY

BEGIN CATCH

-- roll back the transaction because of error

ROLLBACK TRAN;

END CATCH;

</pre></div>

<ul><li><a href="Adaptive_Server_Enterprise" title="Adaptive Server Enterprise">Adaptive Server Enterprise (Sybase)</a></li>

<li><a href="PL/SQL" title="PL/SQL">PL/SQL (Oracle)</a></li>

<li><a href="PL/pgSQL" title="PL/pgSQL">PL/pgSQL (PostgreSQL)</a></li>

<li><a href="SQL/PSM" title="SQL/PSM">SQL/PSM</a> (ISO standard)</li>

<li><a href="Tabular_Data_Stream" title="Tabular Data Stream">Tabular Data Stream</a></li></ul>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

<li id="cite_note-1"><a href="#cite_ref-1">^</a> <a rel="nofollow" class="external text" href="https://www.infoq.com/news/2012/03/T-SQL-2012">"T-SQL Improvements in SQL Server 2012"</a>, Jonathan Allen on Mar 19, 2012, infoq.com

</li>

</ol></div></div>

<div class="mw-heading mw-heading2"><h2 id="External_links">External links</h2></div>

<ul><li><a rel="nofollow" class="external text" href="https://docs.microsoft.com/en-us/sql/t-sql/language-reference">Transact-SQL Reference</a></li>

<li><a rel="nofollow" class="external text" href="http://www.tsql.info">Transact-SQL Tutorial</a></li></ul></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2023-06-08" href="https://en.wikipedia.org/wiki/?title=Transact-SQL&oldid=1159115189">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>